IF exists(SELECT * FROM dbo.sysobjects WHERE name='vk_lesGemiddeldeProcent' AND xtype='P') 
BEGIN
	DROP PROCEDURE vk_lesGemiddeldeProcent
END
GO

CREATE            PROCEDURE vk_lesGemiddeldeProcent(
  @klas varchar(5),
  @periode char(2))
as
set nocount on

declare @hulptabel TABLE (vak char(2),code varchar(6),cijfer float,maximum float)
declare @vakken TABLE  (vak char(2))
declare @vak char(2)

declare vakCursor cursor local for 
select distinct vak from vakrapportCollectieveRekenwijze
	where klas = @klas 
	and periode = @periode 
	and rekenwijze = 'lesgemiddeldeProcent'
for read only

create table  #klassen(klas varchar(5))

open vakCursor
fetch next from vakCursor into @vak

while (@@fetch_status = 0)
begin
  delete from #klassen
  insert into #klassen(klas) exec geefKlassenSamenMet @klas,@vak 

  delete from @hulptabel
  insert into @hulptabel(vak,code,cijfer,maximum)
    select vir.vak,vir.code,vc.cijfer,vc.maximum
    from vakrapportCollectieveRekenwijze as vir
    left join vakrapportCijferresultaten as vc
	on vc.code = vir.code and
            vc.klas = vir.klas and
	     vc.vak = vir.vak and
	      vc.periode = vir.periode
    where vir.rekenwijze = 'lesgemiddeldeProcent' and 
	vir.klas in (select * from #klassen)  and
  	  vir.vak = @vak and
	    vir.periode = @periode and
	      vc.cijfer >= 0 and
	 	vc.maximum > 0

  insert into vakrapportCollectiefBerekend(klas,vak,periode,code,cijfer)
    select @klas,r.vak,@periode,r.code + 'gem',convert(varchar(10),round(sum(cijfer)*100/sum(maximum),0))      from @hulptabel as c
	left join vakrapportCollectieveRekenwijze as r
        	on r.Code = c.code
	           and r.vak = c.vak
   		   and r.periode = @periode
  where r.klas = @klas and
	 r.periode = @periode and
	  r.vak = @vak and
           r.rekenwijze = 'lesgemiddeldeProcent'
  group by r.code,r.vak

  fetch next from vakCursor into @vak
end